﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DBUtility;
using System.Data;
using TierService;//请先添加引用

namespace TierApplication
{

    /// <summary>
    /// 类T_Order。
    /// </summary>
    public partial class T_Order
    {

        //public bool InvoiceApp()
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("update [T_Order] set ");
        //    strSql.Append("InvoiceRcvDate=@InvoiceRcvDate,");
        //    strSql.Append("InvoiceNo=@InvoiceNo,");
        //    strSql.Append("InvoiceDate=@InvoiceDate,");
        //    strSql.Append("InvoiceUserID=@InvoiceUserID,");
        //    strSql.Append("InvoiceTitle=@InvoiceTitle,");
        //    strSql.Append("InvoiceContent=@InvoiceContent,");
        //    strSql.Append("InvoiceMoney=@InvoiceMoney,");
        //    strSql.Append("InvoiceRemark=@InvoiceRemark");
        //    strSql.Append(" where OrderID=@OrderID ");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@InvoiceRcvDate", SqlDbType.DateTime),
        //            new SqlParameter("@InvoiceNo", SqlDbType.VarChar,100),
        //            new SqlParameter("@InvoiceDate", SqlDbType.DateTime),
        //            new SqlParameter("@InvoiceUserID", SqlDbType.VarChar,50),
        //            new SqlParameter("@InvoiceTitle", SqlDbType.VarChar,100),
        //            new SqlParameter("@InvoiceContent", SqlDbType.VarChar,250),
        //            new SqlParameter("@InvoiceMoney", SqlDbType.Decimal,9),
        //            new SqlParameter("@InvoiceRemark", SqlDbType.VarChar,250),
        //            new SqlParameter("@OrderID", SqlDbType.VarChar,10)
        //            };
        //    parameters[0].Value = InvoiceRcvDate;
        //    parameters[1].Value = InvoiceNo;
        //    parameters[2].Value = InvoiceDate;
        //    parameters[3].Value = InvoiceUserID;
        //    parameters[4].Value = InvoiceTitle;
        //    parameters[5].Value = InvoiceContent;
        //    parameters[6].Value = InvoiceMoney;
        //    parameters[7].Value = InvoiceRemark;
        //    parameters[8].Value = OrderID;

        //    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        #region Audit


        public void deAuditStockOut(string sOrderID, string sUserID, string sAuditRemark)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order ");
            strSql.Append("Set OrderStatus='0',AuditUserID='" + sUserID + "',Sign=0,AuditRemark = '" + sAuditRemark + "'"); ;
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public void AuditStockOut(string sOrderID, string sUserID, string sAuditRemark)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("update T_Order ");
            //strSql.Append("Set OrderStatus='2',AuditUserID='" + sUserID + "',AuditDate=getdate(),Sign=-1,AuditRemark = '" + sAuditRemark + "'"); ;
            //strSql.Append(" where OrderID='" + sOrderID + "' ");
            //DbHelperSQL.ExecuteSql(strSql.ToString());
            int idm = 0;
            string sSm = "";
            OrderAudit(sOrderID, sUserID, -1, "", ref idm, ref sSm);
        }


        public void Audit(string sOrderID, string sUserID)
        {
            int idm = 0;
            string sSm = "";

            OrderAudit(sOrderID, sUserID, 0, "", ref idm, ref sSm);

            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("update T_Order ");
            //strSql.Append("Set OrderStatus='2',AuditUserID='" + sUserID + "',AuditDate=getdate()");
            //strSql.Append(" where OrderID='" + sOrderID + "' ");
            //DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public void Audit(string sOrderID, string sUserID, int iSign)
        {
            int idm = 0;
            string sSm = "";

            OrderAudit(sOrderID, sUserID, iSign, "", ref idm, ref sSm);
        }

        public static void OrderAudit(string sOrderID, string sUserID, int iSign, string sAuditRemark, ref int iDm, ref string sSm)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ac_OrderID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_UserID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ai_Sign", SqlDbType.Int, 4 ),
                                            new SqlParameter("@ac_AuditRemark", SqlDbType.VarChar, 250 ),
                                            new SqlParameter("@ac_dm", SqlDbType.Int,4),
                                            new SqlParameter("@ac_sm", SqlDbType.VarChar, 100 )

                                        };
            parameters[0].Value = sOrderID;
            parameters[1].Value = sUserID;
            parameters[2].Value = iSign;
            parameters[3].Value = sAuditRemark;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("dbo.OrderAudit", parameters, "Num");
            iDm = (int)parameters[4].Value;
            sSm = (string)parameters[5].Value;

        }

        public void DeAudit(string sOrderID, string sUserID, string sAuditRemark)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order ");
            strSql.Append("Set OrderStatus='" + (int)Utility.OrderStatus.S0Input + "',Remark1='" + sAuditRemark + "'");
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public void AuditPayApp(string sOrderID, string sUserID, string sPayAuditOpinion)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order ");
            strSql.Append("Set OrderStatus='" + (int)Utility.OrderStatus.S4PayAppAudited + "',PayAuditUserID='" + sUserID + "',PayAuditDate=getdate(),PayAuditOpinion = '" + sPayAuditOpinion + "'");
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public void DeAuditPayApp(string sOrderID, string sUserID, string sAuditRemark)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order ");
            strSql.Append("Set OrderStatus='" + (int)Utility.OrderStatus.S2Audited + "',PayAuditOpinion='" + sAuditRemark + "'");
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }


        public void SubmitAudit(string sOrderID, ref int iDm, ref string sSm)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ac_OrderID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_UserID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_dm", SqlDbType.Int,4),
                                            new SqlParameter("@ac_sm", SqlDbType.VarChar, 100 )

                                        };
            parameters[0].Value = sOrderID;
            parameters[1].Value = TierService.Utility.gUserID;
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("dbo.OrderSubmitAudit", parameters, "Num");
            iDm = (int)parameters[2].Value;
            sSm = (string)parameters[3].Value;

        }

        public void SubmitAudit(string sOrderID, Utility.OrderStatus OrderStatus)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order ");
            strSql.Append("Set OrderStatus='" + (int)OrderStatus + "'");
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        #endregion

        #region User Define
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetOrderList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select '0' IsChecked,a.*,b.InvoiceTitle,b.InvoiceContent,b.PayFee,");
            strSql.Append("(Select ShipNO from T_OrderShip where ShipID=a.ShipID) ShipNO,");
            strSql.Append("(Select CodeName from C_CustSource where CodeID = (Select CustSource from T_Customer where CustID=a.CustID)) CustSourceName");
            strSql.Append(" FROM S_Order a,T_Order b ");
            strSql.Append(" where a.OrderID=b.OrderID " + strWhere);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetBuyToStockList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select '0' IsChecked,a.*,(Select UserName from P_User where UserID=a.POrderUserID) Buyer");
            strSql.Append(" FROM S_Order a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetSaleProductSumList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ProductID,ProductName,UnitName,  ");
            strSql.Append("  SUM(ShpQuantity) ShpQuantity,");
            strSql.Append("  SUM(ShpQuantity*UnitPrice) ShpMoney,");
            strSql.Append("  SUM(ActQuantity) ActQuantity,");
            strSql.Append("  SUM(ActQuantity*UnitPrice) ActMoney,");
            strSql.Append("  (select isnull(Sum(ActQuantity),0) from S_OrderDetail where ProductID=a.ProductID  and POrderID is not null and OrderType='4') BckQuantity,");
            strSql.Append("  (select isnull(Sum(ActQuantity*UnitPrice),0) from S_OrderDetail where ProductID=a.ProductID  and POrderID is not null and OrderType='4') BckMoney ");
            strSql.Append(" FROM S_OrderDetail a ");
            strSql.Append(" where 1=1 " + strWhere);
            strSql.Append(" group by ProductID,ProductName,UnitName");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetSaleProductList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.OrderID,a.OrderDate,a.OrderTypeName, a.ProductID,a.ProductName,");
            strSql.Append(" a.UnitName,a.UnitPrice,a.ShpQuantity,ShpQuantity*UnitPrice ShpMoney,");
            strSql.Append(" a.ActQuantity,ActQuantity*UnitPrice ActMoney,");
            strSql.Append("  (select isnull(Sum(ActQuantity),0) from S_OrderDetail where ProductID=a.ProductID  and POrderID =a.OrderID and OrderType='4') BckQuantity,");
            strSql.Append("  (select isnull(Sum(ActQuantity*UnitPrice),0) from S_OrderDetail where ProductID=a.ProductID  and POrderID=a.OrderID and OrderType='4') BckMoney,");
            strSql.Append(" a.OrderUserIDName, a.OrderStatusName");
            strSql.Append(" FROM S_OrderDetail a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetOrderProductList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select '0' IsChecked,a.* ");
            strSql.Append(" FROM S_OrderDetailBuy a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetBuyList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from  (select '0' IsChecked,a.*, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID) StockSumCount, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderStatus = '2') AuditedStockQuantity, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderStatus <> '2') AuditingStockQuantity, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID) StockSumPrice, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID ");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumMoney");
            strSql.Append(" FROM S_Order a) a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetLoanList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from  (select '0' IsChecked,a.*, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderType='20') StockSumCount, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderStatus = '2' and bb.OrderType='20') AuditedStockQuantity, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderStatus <> '2' and bb.OrderType='20') AuditingStockQuantity, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID) StockSumPrice, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID ");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumMoney");
            strSql.Append(" FROM S_Order a) a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetSaleList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from  (select '0' IsChecked,a.*, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID) StockSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID) StockSumPrice, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID ");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumMoney");
            strSql.Append(" FROM S_Order a) a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetBorrowList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from  (select '0' IsChecked,a.*, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderType='17') StockSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb where aa.OrderID=bb.OrderID and bb.POrderID=a.OrderID and bb.OrderType='17') StockSumPrice, ");
            strSql.Append("(Select sum(ActQuantity) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID ");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumCount, ");
            strSql.Append("(Select sum(ActQuantity*UnitPrice) from T_OrderDetail aa,T_Order bb  where aa.OrderID=bb.OrderID");
            strSql.Append("    and bb.POrderID in (Select OrderID from T_Order where POrderID=a.OrderID)) StockCancelSumMoney");
            strSql.Append(" FROM S_Order a) a ");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetWastageingList(string strWhere)
        {

            SqlParameter[] parameters = { new SqlParameter("@ac_where ", SqlDbType.VarChar, 500) };
            parameters[0].Value = strWhere;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetWastageingList", parameters, "Num");
            return ds;
        }

        public DataSet GetSaleInvoiceingList(string strWhere)
        {

            SqlParameter[] parameters = { new SqlParameter("@ac_where ", SqlDbType.VarChar, 500) };
            parameters[0].Value = strWhere;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleInvoiceingList", parameters, "Num");
            return ds;

            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(" Select *");
            //strSql.Append("   from (select  a.*,(Select CustName from T_Customer where CustID=a.CustID) CustName,");
            //strSql.Append("                 (Select sum(InvoiceMoney)");
            //strSql.Append("                     from T_OrderDetail aa, T_Order bb");
            //strSql.Append("                    where aa.OrderID = bb.OrderID");
            //strSql.Append("                      and bb.POrderID = a.OrderID) OrderInvoiceedMoney");
            //strSql.Append("            FROM T_Order a");
            //strSql.Append(" ) a");
            //strSql.Append("  where 1 = 1");
            //strSql.Append("    and a.OrderType = '10'");
            //strSql.Append("    and IsNull(InvoiceMoney, 0) > IsNull(OrderInvoiceedMoney, 0)");
            //if (strWhere.Trim() != "")
            //{
            //    strSql.Append("  " + strWhere);
            //}
            //return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetOrderDetailByOrderIDInt(string sOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM S_OrderDetailInt a ");
            strSql.Append(" where OrderID='" + sOrderID + "'");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetOrderDetailByOrderIDInt_Loan(string sOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM S_OrderDetailInt_Loan a ");
            strSql.Append(" where OrderID='" + sOrderID + "'");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetStockOutOrderDetailByOrderID(string sOrderID)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(" select a.*,b.BuyOrderID,(Select OrderDate from T_Order where OrderID=b.OrderID) BuyOrderDate,");
            //strSql.Append(" (Select OrderID from T_OrderDetail where OrderDetailID=b.POrderDetailID) StockInOrderID,");
            //strSql.Append(" from S_OrderDetailInt a,T_OrderDetail b");
            //strSql.Append(" where a.OrderDetailID=b.OrderDetailID and  a.OrderID='" + sOrderID + "'");
            //return DbHelperSQL.Query(strSql.ToString());
            string swhere = " and a.OrderID='" + sOrderID + "'";
            SqlParameter[] parameters = { new SqlParameter("@ac_where", SqlDbType.VarChar, 500) };
            parameters[0].Value = swhere;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetOrderDetail", parameters, "Num");
            return ds;

        }

        public static DataSet GetSaleShipByOrderID(string sOrderID)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_OrderID ", SqlDbType.VarChar, 100) };
            parameters[0].Value = sOrderID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipByOrderID", parameters, "Num");
            return ds;
        }

        public static DataSet GetSaleShipByOrderID_Borrow(string sOrderID)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_OrderID ", SqlDbType.VarChar, 100) };
            parameters[0].Value = sOrderID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipByOrderID_Borrow", parameters, "Num");
            return ds;
        }

        public static void CheckStockOutByOrderDetailID(string sOrderDetailID, int iActQty, ref int iDm, ref string sSm)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ac_OrderDetailID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ai_ActQty", SqlDbType.Int, 4 ),
                                            new SqlParameter("@ac_dm", SqlDbType.Int,4),
                                            new SqlParameter("@ac_sm", SqlDbType.VarChar, 100 )

                                        };
            parameters[0].Value = sOrderDetailID;
            parameters[1].Value = iActQty;
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("dbo.CheckStockOutByOrderDetailID", parameters, "Num");
            iDm = (int)parameters[2].Value;
            sSm = (string)parameters[3].Value;
        }

        public static void CheckStockOutByStockInOrderDetailID(string sStockInOrderDetailID, string sStockOutOrderID, int iActQty, ref int iDm, ref string sSm)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ac_StockInOrderDetailID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_StockOutOrderID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ai_ActQty", SqlDbType.Int, 4 ),
                                            new SqlParameter("@ac_dm", SqlDbType.Int,4),
                                            new SqlParameter("@ac_sm", SqlDbType.VarChar, 100 )

                                        };
            parameters[0].Value = sStockInOrderDetailID;
            parameters[1].Value = sStockOutOrderID;
            parameters[2].Value = iActQty;
            parameters[3].Direction = ParameterDirection.Output;
            parameters[4].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("dbo.CheckStockOutByStockInOrderDetailID", parameters, "Num");
            iDm = (int)parameters[3].Value;
            sSm = (string)parameters[4].Value;
        }

        public static void CheckBuyStock(string sOrderID, string sOrderDetailID, string sProductID, string sBatchNo, ref int iDm, ref string sSm)
        {
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ac_OrderID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_OrderDetailID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_ProductID", SqlDbType.VarChar, 10 ),
                                            new SqlParameter("@ac_BatchNo", SqlDbType.VarChar, 20 ),
                                            new SqlParameter("@ac_dm", SqlDbType.Int,4),
                                            new SqlParameter("@ac_sm", SqlDbType.VarChar, 100 )

                                        };
            parameters[0].Value = sOrderID;
            parameters[1].Value = sOrderDetailID;
            parameters[2].Value = sProductID;
            parameters[3].Value = sBatchNo;
            parameters[4].Direction = ParameterDirection.Output;
            parameters[5].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("dbo.CheckBuyStock", parameters, "Num");
            iDm = (int)parameters[4].Value;
            sSm = (string)parameters[5].Value;
            //DataSet ds = CodePortal.DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipByOrderID", parameters, "Num");
            //return ds;

        }


        public static DataSet GetBuyOrderDetail(string @ac_BuyOrderID, string @ac_ProductID)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_BuyOrderID ", SqlDbType.VarChar, 10), 
                                          new SqlParameter("@ac_ProductID ", SqlDbType.VarChar, 10),
                                          new SqlParameter("@ac_OrderDetailID ", SqlDbType.VarChar, 10) 
                                        };
            parameters[0].Value = @ac_BuyOrderID;
            parameters[1].Value = @ac_ProductID;
            parameters[2].Value = "";
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetBuyOrderDetail", parameters, "Num");
            return ds;

        }

        public static DataSet GetBuyOrderDetail(string @ac_OrderDetailID)
        {
            T_OrderDetail mOrderDetailID = new T_OrderDetail();
            mOrderDetailID.GetModel(@ac_OrderDetailID);
            T_Order mOrder = new T_Order();
            mOrder.GetModel(mOrderDetailID.OrderID);

            SqlParameter[] parameters = { new SqlParameter("@ac_BuyOrderID ", SqlDbType.VarChar, 10), 
                                          new SqlParameter("@ac_ProductID ", SqlDbType.VarChar, 10),
                                          new SqlParameter("@ac_OrderDetailID ", SqlDbType.VarChar, 10) 
                                        };
            parameters[0].Value = mOrder.POrderID;
            parameters[1].Value = mOrderDetailID.ProductID;
            parameters[2].Value = @ac_OrderDetailID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetBuyOrderDetail", parameters, "Num");
            return ds;

        }

        public static DataSet GetSaleShipStockProductByProductID(string sProductID)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_ProductID ", SqlDbType.VarChar, 10) };
            parameters[0].Value = sProductID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipStockProductByProductID", parameters, "Num");
            return ds;
        }

        public static DataSet GetSaleShipStockByDepotID(string sProductID,string sDepotID)
        {
            SqlParameter[] parameters = { 
                           new SqlParameter("@ac_ProductID ", SqlDbType.VarChar, 10),
                           new SqlParameter("@ac_DepotID ", SqlDbType.VarChar, 10)             };
            parameters[0].Value = sProductID;
            parameters[1].Value = sDepotID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipStockByDepotID", parameters, "Num");
            return ds;
        }

        public static DataSet GetGetSaleShipStockProductByOrderDetailID(string sOrderDetailID)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_OrderDetailID ", SqlDbType.VarChar, 10) };
            parameters[0].Value = sOrderDetailID;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetSaleShipStockProductByOrderDetailID", parameters, "Num");
            return ds;
        }

        public static DataSet GetOrderDetailByOrderID(string sOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM S_OrderDetail a ");
            strSql.Append(" where OrderID='" + sOrderID + "'");
            return DbHelperSQL.Query(strSql.ToString());
        }


        public static DataSet GetSaleCancelByOrderID(string sOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ProductID,a.ProductName,a.SupplierName,a.UnitName,a.UnitPrice,0 PlnQuantity,0 ShpQuantity,0 BckQuantity, ");
            strSql.Append(" (ShpQuantity-ActQuantity) ActQuantity,(ShpQuantity-ActQuantity)*a.UnitPrice SumPrice ");
            strSql.Append(" FROM S_OrderDetail a ");
            strSql.Append(" where OrderID='" + sOrderID + "' and ShpQuantity > ActQuantity");
            //strSql.Append(" and not exists(");
            //strSql.Append("  select 1 from S_OrderDetail where OrderID in (Select OrderID from T_Order where OrderID=a.OrderID)");
            //strSql.Append("  and ProductID=a.ProductID and ShpQuantity=(a.ShpQuantity-a.ActQuantity)");
            //strSql.Append(" )");
            return DbHelperSQL.Query(strSql.ToString());
        }


        //public void UpdateShipID()
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("update T_Order set OrderStatus='3',Sign=-1,");
        //    strSql.Append("ShipID='" + ShipID + "'");
        //    strSql.Append(" where OrderID='" + OrderID + "' ");
        //    DbHelperSQL.ExecuteSql(strSql.ToString());
        //}

        public void SubmitShip(string sShipID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order set OrderStatus='3',Sign=-1");
            strSql.Append(" where ShipID='" + sShipID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public string GetAddSql()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [T_Order](");
            strSql.Append("OrderID,POrderID,OrderDate,OrderUserID,OrderNo,OrderCode,SupplierID,CustID,OrderType,OrderSort,OrderStatus,DepotID,ToDepotID,AuditUserID,AuditDate,Remark,ConfirmUserID,ConfirmDate,Sign,PayType,PayStatus,Transfer,SaleRemark,ShippingType,RequiredDate,ShipingRequired,ExpressFee,CashFee,OtherFee,CreateDate,CreateUser,Remark1,Remark2,InvoiceTitle,InvoiceContent,PayFee,InvoiceRemark,InvoiceIsNeed");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("'" + OrderID + "',");
            strSql.Append("'" + POrderID + "',");
            strSql.Append("'" + OrderDate + "',");
            strSql.Append("'" + OrderUserID + "',");
            strSql.Append("'" + OrderNo + "',");
            strSql.Append("'" + OrderCode + "',");
            strSql.Append("'" + SupplierID + "',");
            strSql.Append("'" + CustID + "',");
            strSql.Append("'" + OrderType + "',");
            strSql.Append("'" + OrderSort + "',");
            strSql.Append("'" + OrderStatus + "',");
            strSql.Append("'" + DepotID + "',");
            strSql.Append("'" + ToDepotID + "',");
            strSql.Append("'" + AuditUserID + "',");
            strSql.Append("'" + AuditDate + "',");
            strSql.Append("'" + Remark + "',");
            strSql.Append("'" + ConfirmUserID + "',");
            strSql.Append("'" + ConfirmDate + "',");
            strSql.Append("" + Sign + ",");
            strSql.Append("'" + PayType + "',");
            strSql.Append("'" + PayStatus + "',");
            strSql.Append("'" + Transfer + "',");
            strSql.Append("'" + SaleRemark + "',");
            strSql.Append("'" + ShippingType + "',");
            strSql.Append("'" + RequiredDate + "',");
            strSql.Append("'" + ShipingRequired + "',");
            strSql.Append("" + ExpressFee + ",");
            strSql.Append("" + CashFee + ",");
            strSql.Append("" + OtherFee + ",");
            strSql.Append("getdate(),");
            strSql.Append("'" + TierService.Utility.gUserID + "',");
            strSql.Append("'" + Remark1 + "',");
            strSql.Append("'" + Remark2 + "',");
            strSql.Append("'" + InvoiceTitle + "',");
            strSql.Append("'" + InvoiceContent + "',");
            strSql.Append("" + PayFee + ",");
            strSql.Append("'" + InvoiceRemark + "',");
            strSql.Append("'" + InvoiceIsNeed + "'");
            strSql.Append(")");
            return TierService.Utility.GetSqlStringExceptNull(strSql.ToString());
        }

        public void UpdatePrintStatus(string sOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Order Set ");
            strSql.Append("ShipRemark='√'");
            strSql.Append(" where OrderID='" + sOrderID + "' ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }


        public DataSet GetRptSaleAnalysis(string strWhere)
        {
            SqlParameter[] parameters = { new SqlParameter("@ac_where ", SqlDbType.VarChar, 500) };
            parameters[0].Value = strWhere;
            DataSet ds = DBUtility.DbHelperSQL.RunProcedure("dbo.GetRptSaleAnalysis", parameters, "Num");
            return ds;
        }

        #endregion
    }


}

